﻿using KSSModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ImportOldData
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Please input sheet index (only number)");
            string sheetIndexString = Console.ReadLine();
            int sheetIndex = Int32.TryParse(sheetIndexString, out sheetIndex) ? Int32.Parse(sheetIndexString) : 1;
            Console.Write("Please wait while this screen is showing...");
            string path = "c:\\importolddata\\data.xlsx";
            List<tblDoc> ds = new List<tblDoc>();
            List<docPropertyData> dsp = new List<docPropertyData>();
            var result = ReadExcel(path, out ds, out dsp, sheetIndex);

            foreach (var d in ds)
            {
                KSSOnline.Controller.kssForm.AddDoc(d);
            }


            KSSOnline.Controller.kssForm.AddDocProperties(dsp, out dsp);

        }


        private static bool ReadExcel(string FilePath, out List<KSSModel.tblDoc> Docs, out List<KSSModel.docPropertyData> Properties,int sheetIndex)
        {
            Docs = new List<tblDoc>();
            Properties = new List<docPropertyData>();

            FileInfo existingFile = new FileInfo(FilePath);

            using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
            {
                int rowStart = 3;
                string createdBy = "";
                int running = 0;
                do
                {
                    var d = new tblDoc();
                    ExcelWorksheet w = xlPackage.Workbook.Worksheets[sheetIndex];

                    if (w.Cells[rowStart, 6].Value == null)
                    {
                        break;
                    }
                    d.createdBy = w.Cells[rowStart, 1].Value.ToString().Replace("-", "");
                    createdBy = d.createdBy;


                    DateTime created = Convert.ToDateTime(w.Cells[rowStart, 6].Value.ToString());
                    d.id = "D" + created.Day + created.Month.ToString().PadLeft(2, '0') +created.Year.ToString().Substring(2, 2) + running.ToString().PadLeft(3, '0');
                    d.title = w.Cells[rowStart, 3].Value.ToString();
                    d.status = 40;
                    d.score = string.IsNullOrEmpty(w.Cells[rowStart, 7].Value.ToString()) ? 0 : Convert.ToInt32(w.Cells[rowStart, 7].Value);
                    d.departmentName = w.Cells[rowStart, 8].Value.ToString();
                    d.createdByName = w.Cells[rowStart, 9].Value.ToString();
                    d.approver1 = w.Cells[rowStart, 11].Value.ToString().Replace("-", "");
                    d.approver2 = w.Cells[rowStart, 12].Value.ToString().Replace("-", "");

                    DateTime approved1Date = Convert.ToDateTime(w.Cells[rowStart, 14].Value.ToString());
                    DateTime approved2Date = Convert.ToDateTime(w.Cells[rowStart, 15].Value.ToString());
                    DateTime approvedTQMDate = Convert.ToDateTime(w.Cells[rowStart, 16].Value.ToString());

                    d.approved1Date = approved1Date;
                    d.approved2Date = approved2Date;
                    d.createdDate = created;
                    d.approvedQMDate = approvedTQMDate;
                    d.cash = string.IsNullOrEmpty(w.Cells[rowStart, 17].Value.ToString()) ? 0 : Convert.ToInt32(w.Cells[rowStart, 17].Value);

                    Docs.Add(d);


                    var dp = new docPropertyData();
                    dp.docId = d.id;
                    dp.propertyName = "docImage";
                    dp.dataNvarchar = w.Cells[rowStart, 18].Value.ToString() + ".pdf";
                    Properties.Add(dp);

                    var dp1 = new docPropertyData();
                    dp1.docId = d.id;
                    dp1.propertyName = "12";
                    dp1.dataNvarchar = w.Cells[rowStart, 19].Value.ToString().Contains("kaizen").ToString();
                    Properties.Add(dp1);

                    var dp2 = new docPropertyData();
                    dp2.docId = d.id;
                    if (w.Cells[rowStart, 20].Value.ToString().Contains("Manufacturing"))
                    {
                        dp2.propertyName = "15";
                        dp2.dataNvarchar = "True";

                    }
                    else
                    {
                        dp2.propertyName = "16";
                        dp2.dataNvarchar = "True";
                    }

                    Properties.Add(dp2);
                    var dp3 = new docPropertyData();
                    dp3.docId = d.id;
                    switch (w.Cells[rowStart, 21].Value.ToString())
                    {

                        case "S":
                            dp3.propertyName = "22";
                            dp3.dataNvarchar = "True";
                            break;

                        case "Other":
                            dp3.propertyName = "23";
                            dp3.dataNvarchar = "True";
                            break;

                        case "C":
                            dp3.propertyName = "21";
                            dp3.dataNvarchar = "True";
                            break;

                        case "R":
                            dp3.propertyName = "17";
                            dp3.dataNvarchar = "True";
                            break;

                        case "O":
                            dp3.propertyName = "20";
                            dp3.dataNvarchar = "True";
                            break;

                        case "A":
                            dp3.propertyName = "19";
                            dp3.dataNvarchar = "True";
                            break;

                        case "M":
                            dp3.propertyName = "18";
                            dp3.dataNvarchar = "True";
                            break;

                        default:
                            break;


                    }
                    dp3.propertyName = "savecost";
                    dp3.dataNvarchar = "0";
                    Properties.Add(dp3);

                    rowStart++;
                    running++;

                }
                while (!string.IsNullOrEmpty(createdBy));

            }

            return true;
        }
    }
}
